#!/bin/bash
# 比对两个库的表结构，输出差异化，需要提前安装mysql、mysqldiff、mysqldump命令

HOST=localhost
USER=root
PASSWD=1qaz2wsx
PORT=3306


if [ $# -lt 3 ];
then
	echo "Usage: sh $0 source_db_name target_db_name formal_db_name"
	exit 1
fi
source_db_name=$1 	#源库名
target_db_name=$2 	#目标库名
formal_db_name=$3 	#正式库名，用于替换sql的目标库名

# 将源库和目标库的表名导出
mysql -u$USER -p$PASSWD -h$HOST -P$PORT -D $source_db_name -N -B -e "show tables" > .source
mysql -u$USER -p$PASSWD -h$HOST -P$PORT -D $target_db_name -N -B -e "show tables" > .target
>.temp
>.same
>.sametable
>.result
# 找出在源库中有，但在目标库不存在的表
echo "=================================================" >>.temp
echo "$source_db_name exits, $target_db_name not exists" >>.temp
echo "=================================================" >>.temp
for table in $(< .source);
do
	grep -w $table .target
	if [ $? -eq 1 ]; 
	then
		#源库有，目标库没有，需要导出源库的建表语句
		mysqldump -u$USER -p$PASSWD -h$HOST -P$PORT -d --add-drop-table -B $source_db_name --tables $table >> .temp
	else
		#源库有，目标库有，需要写入文件，后续做对比使用
		echo "$table" >> .same
	fi
done

echo "" >>.temp
echo "" >>.temp
echo "" >>.temp
# 找出在源库中不存在，但在目标库存在的表
echo "=================================================" >>.temp
echo "$source_db_name not exits, $target_db_name exists" >>.temp
echo "=================================================" >>.temp
for table in $(< .target);
do
	grep -w $table .source
	if [ $? -eq 1 ]; then
		#源库没有，目标库有，需要生成drop语句
		echo "DROP TABLE IF EXISTS \`$table\`;" >> .temp
	else
		#源库有，目标库有，需要写入文件，后续做对比使用
		echo "$table" >> .same
	fi
done

echo "" >>.temp
echo "" >>.temp
echo "" >>.temp
# 对都存在的表进行表结构对比
cat .same | sort | uniq > .sametable
echo "=================================================" >>.temp
echo "compare result" >>.temp
echo "=================================================" >>.temp
for table in $(< .sametable);
do
	mysqldiff --server1=$USER:$PASSWD@$HOST:$PORT --server2=$USER:$PASSWD@$HOST:$PORT $source_db_name.$table:$target_db_name.$table --changes-for=server2 --difftype=sql >> .temp
	echo "$table comparison completion"
done

# 删除注释
grep -v "#" .temp |grep -v "\/\*\!"|grep -v "\-\-" > .result

# 替换为正式库名
sed "s/$target_db_name/$formal_db_name/g" .result > "$formal_db_name".txt


